package db;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import model.type.EntPerRelationType;


public class EntPerRelationTypeDAO {
	private static final String TABLE = "ent_per_relation_type";	
	private static String onSqlError = "SQL statement fault";
		
	private static final String 
		ID_FIELD = "ent_per_relation_type",			
		TYPE_NAME_FIELD = "type_name";
	
	private static final String INSERT_STATEMENT_STRING = 
			"INSERT INTO "+TABLE+" ("+ID_FIELD+", "+TYPE_NAME_FIELD+") VALUES (?, ?)";
	
	private static final String UPDATE_STATEMENT_STRING = 
			"UPDATE "+TABLE+" SET " +TYPE_NAME_FIELD+" = ? " +
			"WHERE "+ID_FIELD+" = ?";
	
	private static final String DELETE_STATEMENT_STRING =
			"DELETE FROM "+TABLE+" WHERE "+ID_FIELD+" = ?";
	
	private static final String NEXT_ID_STATEMENT_STRING =
			"SELECT "+ID_FIELD+" FROM "+TABLE+" ORDER BY "+ID_FIELD+" DESC LIMIT 1";
	
	private static PreparedStatement insertStatement, updateStatement, deleteStatement, nextIdStatement;
	
	static{
		try {
			insertStatement = ImbiConnection.getConnection().prepareStatement(INSERT_STATEMENT_STRING);
			updateStatement = ImbiConnection.getConnection().prepareStatement(UPDATE_STATEMENT_STRING);
			deleteStatement = ImbiConnection.getConnection().prepareStatement(DELETE_STATEMENT_STRING);
			nextIdStatement = ImbiConnection.getConnection().prepareStatement(NEXT_ID_STATEMENT_STRING);
		} catch (SQLException e) {			
			e.printStackTrace();
			System.exit(0);
		}
	}
	
	public ArrayList<EntPerRelationType> findAll() {			
		try{				
			Statement st = ImbiConnection.getConnection().createStatement();
			st.executeQuery("SELECT * FROM "+TABLE);
			ResultSet r = st.getResultSet();			
			ArrayList<EntPerRelationType> entPerRelationTypes = new ArrayList<EntPerRelationType>();
						
			while(r.next()){				
				entPerRelationTypes.add(
					new EntPerRelationType(
						r.getInt(1), 						
						r.getString(2)					
					)
				);
			}
			return entPerRelationTypes;
		} catch (SQLException s){
				System.out.println(onSqlError);				
				return null;
		}		
	}	
	
	
	public void insert(String type){
		try{
			insertStatement.setInt(1, getNextId());	
			insertStatement.setString(2, type);	
		    insertStatement.executeUpdate();			
		} catch (SQLException s){
				System.out.println(onSqlError);							
		}		
	}
	
	public void update(EntPerRelationType e){
		try{										
			updateStatement.setString(1, e.getType());	       
		    updateStatement.setInt(2, e.getId());
		    updateStatement.executeUpdate();			
		} catch (SQLException s){
				System.out.println(onSqlError);							
		}			
	}
		
	public void delete(EntPerRelationType e){
		try{										
		    deleteStatement.setInt(1, e.getId());		    
		    deleteStatement.executeUpdate();			
		} catch (SQLException s){
				System.out.println(onSqlError);				
		}			
	}	
		
	public int getNextId(){
		try{				
			nextIdStatement.execute();
			ResultSet r = nextIdStatement.getResultSet();			
			r.next();		
			return r.getInt(1) +1;
		} catch (SQLException s){
				System.out.println(onSqlError);				
				return 0;
		}		
	}	
}
